﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Npgsql;

namespace PGMapper
{
    /// <summary>
    /// Provides a mechanism to build high performance PostgreSQL database commands 
    /// with less code than the traditional ADO.NET classes (which this wraps).
    /// </summary>
    public class PostgresCommand
    {
        PostgresDatabase db;
        NpgsqlCommand cmd;
        bool inTransaction;

        public PostgresCommand(PostgresDatabase db, string sql, params object[] positionalParameters)
        {
            this.db = db;

            inTransaction = db.CurrentTransaction != null;

            var connection = inTransaction ? db.CurrentTransaction.Connection : db.GetConnection();
            this.cmd = connection.CreateCommand();
            this.cmd.CommandText = FormatWithParameters(sql, positionalParameters);
        }

        /// <summary>
        /// Gets or sets the SQL command text.
        /// </summary>
        public string Sql
        {
            get { return cmd.CommandText; }
            set { cmd.CommandText = value; }
        }

        #region SQL Text Formatting

        /// <summary>
        /// Append an arbitrary SQL string to the end of our current SQL command text.
        /// </summary>
        public void AppendLine(string sql, params object[] positionalParameters)
        {
            Sql += "\n" + FormatWithParameters(sql, positionalParameters);
        }

        /// <summary>
        /// Add a WHERE clause to our current SQL command text.
        /// </summary>
        public void AddWhere(string criteria, params object[] positionalParameters)
        {
            criteria = FormatWithParameters(criteria, positionalParameters);

            if (Sql.Contains("where"))
                Sql = Sql.Replace("where", "where " + criteria + "\n and");
            else if (Sql.Contains("WHERE"))
                Sql = Sql.Replace("WHERE", "WHERE " + criteria + "\n AND");
            else
                Sql += "\n where " + criteria;
        }

        /// <summary>
        /// Add a parameter to the query directly.  You are responsible for naming
        /// the parameter correctly in the SQL text somewhere.
        /// </summary>
        public void AddParam(string name, object value)
        {
            cmd.Parameters.Add(name, value);
        }

        /// <summary>
        /// Gets or sets parameter objects directly by their name in the SQL text.
        /// </summary>
        public object this[string parameterName]
        {
            get { return cmd.Parameters[parameterName].Value; }
            set
            {
                // the NpgsqlParameterCollection is kind of lame.
                foreach (NpgsqlParameter param in cmd.Parameters)
                    if (param.ParameterName == parameterName)
                    {
                        param.Value = value; // reuse existing parameter object
                        return;
                    }

                AddParam(parameterName, value);
            }
        }

        private string FormatWithParameters(string s, params object[] positionalParameters)
        {
            // interpret direct null as 1 parameter with null value
            if (positionalParameters == null)
                positionalParameters = new object[] { null };

            StringBuilder sb = new StringBuilder();
            string[] split = s.Split('?');

            int numQs = split.Length - 1;
            if (numQs != positionalParameters.Length)
                throw new ArgumentException("The number of '?' parameters in sql fragment '" + s + "' does not match the number of parameters actually provided.");

            for (int i = 0; i < positionalParameters.Length; i++)
            {
                object value = positionalParameters[i];
                int id = cmd.Parameters.Count;
                string parameterName = "@p" + id;
                sb.Append(split[i]);
                sb.Append(parameterName);
                cmd.Parameters.Add(parameterName, value);
            }

            // last fragment
            sb.Append(split[split.Length - 1]);
            return sb.ToString();
        }

        #endregion

        #region Execute Methods

        /// <summary>
        /// Executes a command that returns no data. Returns the number of rows affected by the command.
        /// </summary>
        public int Execute()
        {
            DumpToConsole();

            if (!inTransaction)
                cmd.Connection.Open();

            try { return cmd.ExecuteNonQuery(); }
            finally {
                if (!inTransaction)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Executes a command that returns a single object.
        /// </summary>
        public object ExecuteScalar()
        {
            DumpToConsole();

            if (!inTransaction)
                cmd.Connection.Open();

            try { return cmd.ExecuteScalar(); }
            finally
            {
                if (!inTransaction)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Returns a cursor-based data reader from the DB.  Remember to close it when
        /// you're finished!
        /// </summary>
        public IDataReader ExecuteReader()
        {
            DumpToConsole();

            if (!inTransaction)
                cmd.Connection.Open();

            return cmd.ExecuteReader(inTransaction ? CommandBehavior.Default : CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// Executes the command and performs the given action against all rows returned.
        /// </summary>
        public void Execute(Action<IDataReader> action)
        {
            using (IDataReader reader = ExecuteReader())
                while (reader.Read())
                    action(reader);
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// </summary>
        public PostgresRow ExecuteFirstRow()
        {
            using (IDataReader reader = ExecuteReader())
                return reader.Read() ? new PostgresRow(reader) : null;
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// If more than one row is found, an exception is thrown. If no rows were found, returns null.
        /// </summary>
        public PostgresRow ExecuteRow()
        {
            using (IDataReader reader = ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                var row = new PostgresRow(reader);

                if (reader.Read())
                    throw new Exception("More than one row found!");

                return row;
            }
        }

        /// <summary>
        /// Executes the command and returns the first row as a map from column name to row value.
        /// If more than one row is found, or if no rows were found, an exception is thrown.
        /// </summary>
        public PostgresRow ExecuteOneRow()
        {
            PostgresRow row = ExecuteRow();
            if (row == null) throw new Exception("Expected to find one row, instead found none.");
            return row;
        }

        /// <summary>
        /// Executes the command and loads the result into a DataTable.
        /// </summary>
        public DataTable ExecuteTable()
        {
            using (IDataReader reader = ExecuteReader())
            {
                var table = new DataTable();
                table.Load(reader);
                return table;
            }
        }

        /// <summary>
        /// Executes a command that returns a single object of the given type. If T is not a 
        /// value type or a string and no rows were returned, the result is default(T).
        /// </summary>
        public T ExecuteSingle<T>()
        {
            // if this is a complex class, we'll need to potentially explore the type and build it
            if (typeof(T).IsValueType || typeof(T) == typeof(string))
                return (T)Convert.ChangeType(ExecuteScalar(), typeof(T));
            else
                return ExecuteSingleObject<T>();
        }

        private T ExecuteSingleObject<T>()
        {
            var mapped = new MappedType<T>();
            var row = ExecuteRow();

            if (row == null)
                return default(T);

            return mapped.CreateInstance(db, row);
        }

        /// <summary>
        /// Executes the command and loads the results of the first column into a strongly typed list.
        /// </summary>
        public List<T> ExecuteList<T>()
        {
            // if this is a complex class, we'll need to potentially explore the type and build it
            if (!typeof(T).IsValueType && typeof(T) != typeof(string))
                return ExecuteCompositeList<T>();

            var list = new List<T>();            

            using (IDataReader reader = ExecuteReader())
                while (reader.Read())
                    list.Add((T)Convert.ChangeType(reader[0], typeof(T)));

            return list;
        }

        private List<T> ExecuteCompositeList<T>()
        {
            var list = new List<T>();
            var mapped = new MappedType<T>();

            using (IDataReader reader = ExecuteReader())
                while (reader.Read())
                {
                    var row = new PostgresRow(reader);
                    list.Add(mapped.CreateInstance(db, row));
                }

            return list;
        }

        #endregion

        #region Dump to Console

        /// <summary>
        /// Prints a debuggable query with parameters inserted to the console. You can cut and
        /// paste it into a query analyzer.
        /// </summary>
        void DumpToConsole()
        {
            if (!db.PrintSqlToConsole) return;

            string sql = Sql;

            foreach (NpgsqlParameter p in cmd.Parameters)
            {
                string name = p.ParameterName;
                object value = p.Value;

                if (value is string || value is DateTime)
                    sql = sql.Replace(name, "'" + value + "'");
                else if (value is bool)
                    sql = sql.Replace(name, value.ToString().ToLower());
                else
                    sql = sql.Replace(name, value.ToString());
            }

            var reader = new StringReader(sql);

            while (true)
            {
                string line = reader.ReadLine();
                if (line == null) break;
                Console.WriteLine(line.Trim());
            }
        }

        #endregion
    }
}
